package com.colabo.j2ee.web.core.dao.mediator.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.ibatis.SqlMapClientTemplate;

import com.colabo.j2ee.web.core.dao.extension.ibatis.IBatisSqlExecutor;
import com.colabo.j2ee.web.core.dao.mediator.IDefaultMediator;
import com.colabo.j2ee.web.core.exception.MediatorException;
import com.colabo.j2ee.web.core.util.ReflectUtil;
import com.colabo.j2ee.web.core.vo.RecordObject;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;

/**
 * mediator实现，绑定Hibernate,iBatis,JDBC三种数据访问技术,完成增删，条件查询，批量更新等操作
 */
public class DefaultMediatorImpl implements IDefaultMediator {
	protected final Log logger = LogFactory.getLog(this.getClass());
	/**
	 * 以私有成员变量保存SessionFactory
	 */
	private SessionFactory sessionFactory;

	/**
	 * 以私有成员变量保存DataSource
	 */
	private DataSource dataSource;

	/**
	 * 以私有成员变量保存SqlMapClient
	 */
	private SqlMapClient sqlMapClient;

	/**
	 * 以私有成员变量保存iBatis模板SqlMapClientTemplate
	 */
	private SqlMapClientTemplate sqlMapClientTemplate;

	/**
	 * 以私有成员变量保存Hibernate模板HibernateTemplate
	 */
	private HibernateTemplate hibernateTemplate;

	/**
	 * 以私有成员变量保存Jdbc模板JdbcTemplate
	 */
	private JdbcTemplate jdbcTemplate;

	/**
	 * 设置注入SessionFactory必须的setter方法(系统自动调用)
	 * 
	 * @param sessionFactory
	 *            会话工厂，配置数据源及事务
	 */
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	/**
	 * 设置注入DataSource必须的setter方法(系统自动调用)
	 * 
	 * @param ds
	 *            配置数据源
	 */
	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
	}

	/**
	 * 设置注入SqlMapClient必须的setter方法(系统自动调用)
	 * 
	 * @param sqlMapClient
	 *            会话工厂，配置数据源及事务
	 */
	public void setSqlMapClient(SqlMapClient sqlMapClient) {
		this.sqlMapClient = sqlMapClient;
	}

	/**
	 * 获取JdbcTemplate模板对象
	 * 
	 * @return JdbcTemplate jdbc模板对象
	 */
	public JdbcTemplate getJdbcTemplate() throws MediatorException {
		// 首先检查原来的JdbcTemplate实例是否存在
		if (this.jdbcTemplate == null) {
			// 如果不存在，则创建一个HibernateTemplate实例
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		}
		return this.jdbcTemplate;
	}

	/**
	 * 获取Hibernate模板对象
	 * 
	 * @return HibernateTemplate Hibernate模板对象
	 */
	public HibernateTemplate getHibernateTemplate() throws MediatorException {
		// 首先检查原来的HibernateTemplate实例是否存在
		if (this.hibernateTemplate == null) {
			// 如果不存在，则创建一个HibernateTemplate实例
			this.hibernateTemplate = new HibernateTemplate(this.sessionFactory);
		}
		return this.hibernateTemplate;
	}

	/**
	 * 获取iBatis模板对象
	 * 
	 * @return SqlMapClientTemplate iBatis模板对象
	 */
	public SqlMapClientTemplate getSqlMapClientTemplate() throws MediatorException {
		// 首先检查原来的SqlMapClientTemplate实例是否存在
		if (this.sqlMapClientTemplate == null) {
			// 如果不存在，则创建一个SqlMapClientTemplate实例
			this.sqlMapClientTemplate = new SqlMapClientTemplate(this.sqlMapClient);
		}
		return this.sqlMapClientTemplate;
	}

	public Connection getConnection() throws MediatorException {
		Connection rs = null;
		try {
			rs = dataSource.getConnection();
		} catch (SQLException se) {
			throw new MediatorException(se);
		}
		return rs;
	}

	private SqlExecutor sqlExecutor;

	public SqlExecutor getSqlExecutor() {
		return sqlExecutor;
	}

	public void setSqlExecutor(SqlExecutor sqlExecutor) {
		this.sqlExecutor = sqlExecutor;
	}

	public void setEnableCustomizedPaged(boolean enableCustomizedPaged) {
		if (sqlExecutor instanceof IBatisSqlExecutor) {
			((IBatisSqlExecutor) sqlExecutor).setEnableCustomizedPaged(enableCustomizedPaged);
		}
	}

	/**
	 * 其中的initialize方法执行注入，此方法在spring Beans 配置中指定为init-method。 由于sqlExecutor是
	 * com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient的私有成员，
	 * 且没有公开的set方法，所以此处通过反射绕过java的访问控制
	 * 
	 * @throws Exception
	 */
	public void initialize() throws Exception {
		if (sqlExecutor != null) {
			SqlMapClient sqlMapClient = getSqlMapClientTemplate().getSqlMapClient();
			if (sqlMapClient instanceof ExtendedSqlMapClient) {
				ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient).getDelegate(), "sqlExecutor",
						SqlExecutor.class, sqlExecutor);
			}
		}
	}

	public int delete_Hibernate_ComposedQuery(final String composedSQL, final Object[] params) throws MediatorException {
		int rs = 0;
		// 用Hibernate的自定义sql代码中完成,可以是update【没有版本控制功能，需要自己处理，所以不赞成使用】，delete和insert操作，query是sql原生态语句
		rs = ((Integer) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
			public Object doInHibernate(Session session){
				SQLQuery sqlq = session.createSQLQuery(composedSQL);
				if (params != null) {
					int length = params.length;
					for (int i = 0; i < length; i++) {
						sqlq.setParameter(i, params[i]);
					}
				}
				int rtn = sqlq.executeUpdate();
				return new Integer(rtn);
			}
		})).intValue();
		return rs;
	}

	public int delete_Hibernate_NamedQuery(final String queryID, final Object[] params) throws MediatorException {
		int rs = 0;
		// 用Hibernate的命名查询完成，可以是update【如有版本控制要注意versioned关键字】，delete，insert【不赞成】操作，query是hql语句名
		rs = ((Integer) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
			public Object doInHibernate(Session session) {
				Query queryCMd = session.getNamedQuery(queryID);
				String queryString = queryCMd.getQueryString();
				Query sqlq = session.createQuery(queryString);
				int length = params.length;
				for (int i = 0; i < length; i++) {
					sqlq.setParameter(i, params[i]);
				}
				int rtn = sqlq.executeUpdate();
				return new Integer(rtn);
			}
		})).intValue();
		return rs;
	}

	public int delete_Hibernate_Object(RecordObject vo) throws MediatorException {
		getHibernateTemplate().delete(vo);

		return 0;

	}

	public int delete_iBatis(String queryID, Object[] params) throws MediatorException {
		// 用iBatis的查询完成,此时参数params保存在index=0的位置
		getSqlMapClientTemplate().delete(queryID, params[0]);
		return 1;
	}

	public int delete_JDBC(String sql, Object[] params) throws MediatorException {
		// 用jdbc完成,此时的query是个完整的sql语句，params不起作用
		getJdbcTemplate().execute(sql);
		return 1;
	}

	public int deleteByID_Hibernate_Object(Class cls, Serializable id) throws MediatorException {
		getHibernateTemplate().delete(findByID_Hibernate_Get(cls, id));
		return 1;
	}

	public Object[] find_Hibernate_ComposedHQL(final String hql, final String[] propertyNames,
			final String[] operators, final Object[] values, final int offset, final int size,
			final boolean isTotalSize, final String orderBy, final String groupBy, final String otherCause)
			throws MediatorException {

		long l1 = System.currentTimeMillis();
		Map map = (Map) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session){
				Query query;
				String countSql;
				String fullSql;
				Integer count = new Integer(0);
				Map map = new HashMap();
				String where = "";

				if (propertyNames != null && propertyNames.length > 0 && values != null && values.length > 0) {
					for (int i = 0; i <= propertyNames.length - 1; i++) {
						if ("".equals(where)) {
							where = " where ";
						} else {
							where += "and ";
						}
						if (operators != null && operators[i].equalsIgnoreCase("isnull")) {
							where += propertyNames[i] + " is null ";
						} else if (operators != null && operators[i].equalsIgnoreCase("isnotnull")) {
							where += propertyNames[i] + " is not null ";
						} else if (operators != null && operators[i].equalsIgnoreCase("isempty")) {
							where += propertyNames[i] + " = '' ";
						} else if (operators != null && operators[i].equalsIgnoreCase("isnotempty")) {
							where += propertyNames[i] + " <> '' ";
						} else {
							where += propertyNames[i]
									+ (operators == null || operators[i] == null ? "=" : " " + operators[i]) + " ? ";
						}
					}

					fullSql = hql + where;
					fullSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
					fullSql += orderBy == null || orderBy.trim().equals("") ? "" : " order by " + orderBy;

					fullSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

					query = session.createQuery(fullSql);

					for (int i = 0; i <= values.length - 1; i++) {
						if (operators != null && operators[i].equalsIgnoreCase("isnull")){
							continue;
						}
						if (operators != null && operators[i].equalsIgnoreCase("isnotnull")){
							continue;
						}
						if (operators != null && operators[i].equalsIgnoreCase("isempty")){
							continue;
						}
						if (operators != null && operators[i].equalsIgnoreCase("isnotempty")){
							continue;
						}
						query.setParameter(i, values[i]);
					}

				} else {

					fullSql = hql + where;

					fullSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
					fullSql += orderBy == null || orderBy.trim().equals("") ? "" : " order by " + orderBy;

					fullSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

					query = session.createQuery(fullSql);
				}

				// 如果需要统计本次查询总记录数
				if (isTotalSize) {
					// 生成统计总数查询语句（不能累加order by ，否则效率会受影响）
					countSql = hql + where;

					countSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
					countSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

					// 生成查询总记录的hql语句。在hql中，不允许在select段内使用子查询，否则无法依靠from关键字分解hql语句
					// 如不允许这种用法：select a,b,(select c from table1) as d from
					// table2 ...
					countSql = "select count(*) from " + countSql.substring(countSql.toLowerCase().indexOf("from") + 5);
					Query query2 = session.createQuery(countSql);

					if (values != null) {
						for (int i = 0; i <= values.length - 1; i++) {
							if (operators != null && operators[i].equalsIgnoreCase("isnull")){
								continue;
							}
							if (operators != null && operators[i].equalsIgnoreCase("isnotnull")){
								continue;
							}
							if (operators != null && operators[i].equalsIgnoreCase("isempty")){
								continue;
							}
							if (operators != null && operators[i].equalsIgnoreCase("isnotempty")){
								continue;
							}
							query2.setParameter(i, values[i]);
						}
					}
					// 输出统计总数查询语句到log
					logger.debug("countSql=" + query2.getQueryString());
					if (query2.list() != null && !query2.list().isEmpty()) {
						count = new Integer(String.valueOf(query2.list().get(0)));
					}
				}

				if ((offset >= 0) && (size > 0)) {
					query.setFirstResult(offset);
					query.setMaxResults(size);
				} 

				// 输出查询语句到log
				logger.debug("fullSql=" + query.getQueryString());

				map.put("list", query.list());
				map.put("count", count);
				return map;
			}
		});

		long l2 = System.currentTimeMillis();
		logger.debug("Execute query time:" + (l2 - l1) + "ms");
		return new Object[] { map.get("list"), map.get("count") };
	}

	public Object[] find_Hibernate_NamedQuery(final String queryID, final Object[] params, final int offset,
			final int size, final boolean isTotalSize) throws MediatorException {
		return (Object[]) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session){
				Query query = session.getNamedQuery(queryID);
				setQueryParameter(query, params);
				if ((offset >= 0) && (size > 0)) {
					query.setFirstResult(offset);
					query.setMaxResults(size);
				} 
				List result = query.list();

				Integer count = Integer.valueOf("0");
				if (isTotalSize) {
					if (!result.isEmpty()) {
						String queryString = query.getQueryString();
						String countQueryString = "select count(*) from "
								+ queryString.substring(queryString.toLowerCase().indexOf("from") + 5);
						Query countQuery = session.createSQLQuery(countQueryString);
						setQueryParameter(countQuery, params);
						count = new Integer(String.valueOf(countQuery.list().get(0)));
					}
				}
				Object[] objs = new Object[] { result, count };
				return objs;
			}

			private void setQueryParameter(Query query, Object[] params) {
				if (params != null) {
					for (int i = 0; i < params.length; i++) {
						query.setParameter(i, params[i]);
					}
				}
			}
		});
	}

	public List find_iBatis(String queryName, Object para, int offset, int size) throws MediatorException {
		// 希望使用平台优化过的分页机制。但启用平台对iBtatis分页的优化机制并不等于就能够用，还要看当前数据库本身是否支持。
		setEnableCustomizedPaged(true);
		return getSqlMapClientTemplate().queryForList(queryName, para, offset, size);
	}

	public List find_iBatis(String queryName, Object para) throws MediatorException {
		return find_iBatis(queryName, para, 0, -999999);
	}

	public List findAll_Hibernate_ComposedHQL(String composedHQL) throws MediatorException {
		// 用Hibernate的find接口完成，自定义hql语句操作，如select a , b from AVO where c = ?
		// ,返回的List中是对象数组Objext[]组成的
		// 用Hibernate的find接口完成，自定义hql操作，如select new BVO(a , b ) from AVO where c
		// = ? ,返回的List中是BVO对象组成的
		// 因此，hql语句的不同，导致返回值List中的含义也是不一样的，需要区别对待。
		return getHibernateTemplate().find(composedHQL);
	}

	public List findAll_Hibernate_NamedQuery(String queryID) throws MediatorException {
		// 由于是findAll，因此不需要参数
		List list = (getHibernateTemplate().findByNamedQuery(queryID));
		return list;
	}

	public List findAll_Hibernate_Object(Class cls) throws MediatorException {
		// 用Hibernate的loadAll接口完成
		return getHibernateTemplate().loadAll(cls);
	}

	public List findAll_iBatis(String queryID) throws MediatorException {
		// 用iBatis的queryForList完成，
		// 本方法实现最好在映射文件中的查询语句中有resultclass或者resultmap，这样能够使返回列表中是由对象组成的。
		return getSqlMapClientTemplate().queryForList(queryID);
	}

	public List findAll_JDBC(String sql) throws MediatorException {
		// 用jdbc的queryForList完成,返回值是由Map组成的List，Map中每个key就是返回值中的列名
		// 如query="SELECT NAME,SEX,AGE FROM
		// EMPLOYEE",则每个item就是map，而map.get(NAME)就能却道name独赢的值。
		return getJdbcTemplate().queryForList(sql);
	}

	public RecordObject findByID_Hibernate_ComposedHQL(String composedHQL, Serializable id) throws MediatorException {
		// 用Hibernate的find方法完成，使用自定义的hql语句,但语句中必须是如下格式：
		// select new xxxVO（属性域a,属性域b....） from xxxVO where 属性域c=？....
		// 没有上面的new xxxVO（）格式，则返回值将是Object[]组成的List，无法解析出一个ValueObject
		List list = (getHibernateTemplate().find(composedHQL, id));
		Iterator it = list.iterator();
		RecordObject vo = null;
		while (it.hasNext()) {
			vo = (RecordObject) it.next();
			break;
		}
		if (vo != null){
			// 主动刷新缓存，以保证数据库和缓存同步
			getHibernateTemplate().getSessionFactory().getCurrentSession().refresh(vo);
		}
		return vo;
	}

	public RecordObject findByID_Hibernate_Get(Class cls, Serializable id) throws MediatorException {
		// 用Hibernate的get方法完成
		RecordObject vo = null;
		vo = (RecordObject) (getHibernateTemplate().get(cls, id));
		if (vo != null){
			// 主动刷新缓存，以保证数据库和缓存同步
			getHibernateTemplate().getSessionFactory().getCurrentSession().refresh(vo);
		}
		return vo;
	}

	public RecordObject findByID_Hibernate_Load(Class cls, Serializable id) throws MediatorException {
		// 用Hibernate的load方法完成
		RecordObject vo = null;
		vo = (RecordObject) (getHibernateTemplate().load(cls, id));
		if (vo != null){
			// 主动刷新缓存，以保证数据库和缓存同步
			getHibernateTemplate().getSessionFactory().getCurrentSession().refresh(vo);
		}
		return vo;
	}

	public RecordObject findByID_Hibernate_NamedQuery(String queryID, Serializable id) throws MediatorException {
		// 用Hibernate的findByNamedQuery方法完成，使用命名查询的hql语句
		// 这里的query是个query name，并且只有在参数为一个的情况下可用本方法，参数以?表示
		// 预定义的语句中，必须有return alias，否则无法确保对象取出。
		// 预定义的语句中，是直接对数据表操作的，但返回值不能是return alias的部分，可以超过它但不能少于它
		List list = (getHibernateTemplate().findByNamedQuery(queryID, id));
		Iterator it = list.iterator();
		RecordObject vo = null;
		while (it.hasNext()) {
			vo = (RecordObject) it.next();
			break;
		}
		if (vo != null){
			// 主动刷新缓存，以保证数据库和缓存同步
			getHibernateTemplate().getSessionFactory().getCurrentSession().refresh(vo);
		}
		return vo;

	}

	public RecordObject findByID_iBatis(String queryID, Serializable id) throws MediatorException {
		// 用iBatis的queryForObject完成
		return (RecordObject) (getSqlMapClientTemplate().queryForObject(queryID, id));
	}

	public int save_Hibernate_ComposedQuery(final String composedSQL, final Object[] params) throws MediatorException {
		// 用Hibernate的自定义sql代码中完成,可以是update【没有版本控制功能，需要自己处理，所以不赞成使用】，delete和insert操作，query是sql原生态语句
		{
			int rs = 0;
			rs = ((Integer) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
				public Object doInHibernate(Session session) {
					SQLQuery sqlq = session.createSQLQuery(composedSQL);
					if (params != null) {
						int length = params.length;
						for (int i = 0; i < length; i++) {
							sqlq.setParameter(i, params[i]);
						}
					}
					int rtn = sqlq.executeUpdate();
					return new Integer(rtn);
				}
			})).intValue();
			return rs;
		}

	}

	public int save_Hibernate_Object(RecordObject vo) throws MediatorException {
		int rs = 0;
		// 用Hibernate对象操作完成
		getHibernateTemplate().save(getHibernateTemplate().merge(vo));
		rs = 1;
		return rs;
	}

	public int save_iBatis(String queryID, Object[] params) throws MediatorException {
		// 用iBatis完成 ,此时参数params保存在index=0的位置
		int rs = 0;
		if (params == null || params.length == 0){
			getSqlMapClientTemplate().insert(queryID, null);
		}else{
			getSqlMapClientTemplate().insert(queryID, params[0]);
		}
		rs = 1;
		return rs;

	}

	public int save_JDBC(String sql, Object[] params) throws MediatorException {
		// 用jdbc完成,此时的query是个完整的sql语句，params不起作用
		getJdbcTemplate().execute(sql);
		return 1;
	}

	public int update_Hibernate_ComposedQuery(final String composedSQL, final Object[] params) throws MediatorException {
		// 用Hibernate的自定义sql代码中完成,可以是update【没有版本控制功能，需要自己处理，所以不赞成使用】，delete和insert操作，query是sql原生态语句
		// ****有集群部署的需求下，不能通过本方法来实现update操作。****
		int rs = 0;
		rs = ((Integer) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
			public Object doInHibernate(Session session){
				SQLQuery sqlq = session.createSQLQuery(composedSQL);
				if (params != null) {
					int length = params.length;
					for (int i = 0; i < length; i++) {
						sqlq.setParameter(i, params[i]);
					}
				}
				int rtn = sqlq.executeUpdate();
				return new Integer(rtn);
			}
		})).intValue();
		return rs;
	}

	public int update_Hibernate_NamedQuery(final String queryID, final Object[] params) throws MediatorException {
		int rs = 0;
		// 用Hibernate的命名查询完成，借助回调机制实现，可以是update【如有版本控制要注意versioned关键字】，delete，insert【不赞成】操作，query是hql语句名
		rs = ((Integer) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
			public Object doInHibernate(Session session) {
				Query queryCMd = session.getNamedQuery(queryID);
				String queryString = queryCMd.getQueryString();
				Query sqlq = session.createQuery(queryString);
				int length = params.length;
				for (int i = 0; i < length; i++) {
					sqlq.setParameter(i, params[i]);
				}
				int rtn = sqlq.executeUpdate();
				return new Integer(rtn);
			}
		})).intValue();
		return rs;
	}

	public int update_Hibernate_Object(RecordObject vo) throws MediatorException {
		// 用Hibernate对象操作完成
		getHibernateTemplate().update(getHibernateTemplate().merge(vo));
		return 1;
	}

	public int update_iBatis(String queryID, Object[] params) throws MediatorException {
		// 用iBatis完成 ,此时参数params保存在index=0的位置
		getSqlMapClientTemplate().update(queryID, params[0]);
		return 1;
	}

	public int update_JDBC(String sql, Object[] params) throws MediatorException {
		// 用jdbc完成,此时的query是个完整的sql语句，params不起作用
		getJdbcTemplate().execute(sql);
		return 1;
	}
}
